
*** Prepare the data
cap confirm file "${ddata}ACS2021_raw_sums.dta"
if _rc {
do "${code}setup_acs"
drop if year==2022
save "${ddata}ACS2021_raw_sums.dta", replace
}

use "${ddata}ACS2021_raw_sums.dta", clear

*keeping only household heads
keep if relate==1
*keeping the relevant time period
*keep if (year==2020 & month>=7) | (year==2021 & month<=11)

gen white = 0
replace white = 1 if race==1

*for age you can use variable age
label drop age_lbl

gen male = 0
replace male = 1 if sex==1
gen female = 0
replace female = 1 if sex==2


* you'll need to construct an income variable similar to what we do in the sce. 
cap drop hh_income_medians
gen hh_income_medians = .
replace hh_income_medians = 5000 if hhincome< 10000 //Under $10k
replace hh_income_medians = 15000 if hhincome>=10000 & hhincome < 20000 //$10k-20k
replace hh_income_medians = 25000 if hhincome>=20000 & hhincome < 30000 //$20k-30k
replace hh_income_medians = 35000 if hhincome>=30000 & hhincome < 40000 //$30k-40k
replace hh_income_medians = 45000 if hhincome>=40000 & hhincome < 50000 //$40k-50k
replace hh_income_medians = 55000 if hhincome>=50000 & hhincome < 60000 //$50k-60k
replace hh_income_medians = 67500 if hhincome>=60000 & hhincome < 75000 //$60k-75k
replace hh_income_medians = 87500 if hhincome>=75000 & hhincome < 100000 //$75k-100k
replace hh_income_medians = 125000 if hhincome>=100000 & hhincome < 150000 //$100k-150k
replace hh_income_medians = 175000 if hhincome>=150000 & hhincome < 200000 //$150k-200k
replace hh_income_medians = 225000 if hhincome>=200000 & hhincome!=. //$200k+

replace hh_income_medians = hh_income_medians/1000

gen college = 0
replace college = 1 if educ>=10

*use the region variable, which oddly gives you the division and then you can map that to region
cap drop census_region
gen census_region = ""
replace census_region = "Northeast" if region == 11 //New England Division
replace census_region = "Northeast" if region == 12 //Middle Atlantic
replace census_region = "Midwest" if region == 21 //E. N. Central
replace census_region = "Midwest" if region == 22 //W. N. Central
replace census_region = "South" if region == 31 //South Atlantic
replace census_region = "South" if region == 32 // E. S. Central
replace census_region = "South" if region == 33 //W. S. Central
replace census_region = "West" if region == 41 // Mountain
replace census_region = "West" if region == 42 // Pacific


gen northeast = 0
replace northeast = 1 if census_region== "Northeast"
gen midwest = 0
replace midwest = 1 if census_region== "Midwest"
gen south = 0
replace south = 1 if census_region== "South"
gen west = 0
replace west = 1 if census_region== "West"


gen married = 0
replace married = 1 if inrange(marst, 1, 2)


*cps only has children under age 5. can we calculate that in the sce? if so, let's change ours
*so that it's consistent with the acs. 

*use variable nchlt5 for the # of kids under age 5.
gen has_children_under_5 = .
replace has_children_under_5 = 1 if nchlt5 > 0 & !mi( nchlt5 )
replace has_children_under_5 = 0 if nchlt5 == 0 & !mi( nchlt5 )

*nchild shows the number of children in the household. that's the closest we can get to # of children under age 18.
gen has_children_under_18 = .
replace has_children_under_18 = 1 if nchild > 0 & !mi( nchild )
replace has_children_under_18 = 0 if nchild == 0 


gen employed = 0
replace employed = 1 if empstat==1
replace employed = . if empstat==0

gen unemployed = 0
replace unemployed = 1 if empstat==2
replace unemployed = . if empstat==.

gen OLF = 0
replace OLF = 1 if empstat==3
replace OLF = . if empstat==.

gen working_ft = 0 if !missing(empstat)
replace working_ft = 1 if uhrswork>=35 

gen acs = 1

save "${ddata}tempacs.dta", replace


use "${ddata}cleaned_data_wACSweight.dta", clear 


gen female = 1 - male

gen has_children_under_5 = .
replace has_children_under_5 = 1 if residence_children_under6 > 0 & residence_children_under6!=.
replace has_children_under_5 = 0 if residence_children_under6 == 0

gen has_children_under_18 = .
replace has_children_under_18 = 1 if residence_children_under18 > 0 & residence_children_under18 !=.
replace has_children_under_18 = 0 if residence_children_under18 == 0

gen south = current_region == "region_south"
gen midwest = current_region == "region_midwest"
gen northeast = current_region == "region_northeast"
gen west = current_region == "region_west"

cap drop working_ft
gen working_ft = q10_1==1 
replace working_ft = . if q10_1==.

gen employed = (q10_1==1 | q10_2 == 1 | q10_5 == 1)
replace employed = . if q10_1==. & q10_2==. & q10_5==.

gen unemployed = (q10_3==1 | q10_4 == 1)
replace unemployed = . if q10_3==. & q10_4==.
replace unemployed = 0 if employed == 1

gen OLF = (q10_6 == 1 | q10_7 == 1 | q10_8 == 1 | q10_9 == 1 | q10_10 == 1)
replace OLF = . if (q10_6 == . & q10_7 == . & q10_8 == . & q10_9 == . & q10_10 == .)
replace OLF = 0 if employed == 1 | unemployed == 1

gen hh_income_medians = . 
replace hh_income_medians = 5000 if hh_income == "<$10k"
replace hh_income_medians = 15000 if hh_income == "$10k-20k"
replace hh_income_medians = 25000 if hh_income == "$20k-30k"
replace hh_income_medians = 35000 if hh_income == "$30k-40k"
replace hh_income_medians = 45000 if hh_income == "$40k-50k"
replace hh_income_medians = 55000 if hh_income == "$50k-60k"
replace hh_income_medians = 67500 if hh_income == "$60k-75k"
replace hh_income_medians = 87500 if hh_income == "$75k-100k"
replace hh_income_medians = 125000 if hh_income == "$100k-150k"
replace hh_income_medians = 175000 if hh_income == "$150k-200k"
replace hh_income_medians = 225000 if hh_income == "$200k+"
replace hh_income_medians = hh_income_medians/1000

gen acs = 0


append using "${ddata}tempacs.dta"
erase "${ddata}tempacs.dta"

local demvar white age hh_income_medians college midwest northeast south west married has_children_under_5 has_children_under_18 ///
			working_ft employed unemployed OLF ///
				 
local labels "White" "Age" "HH Income (\textdollar1000s)" "College" "Midwest" "Northeast" "South" "West" "Married" ///
			"Has Child(ren) Under Age 5 or 6$ds^{*}$ds" "Has Child(ren) Under Age 18" ///
			"Working FT" "Employed" "Unemployed" "Out of Labor Force" 

cap file close table
file open table using "${o}AppTable1.tex", write replace
file write table "\begin{tabular}{lllll}" _n
file write table "\toprule" _n
file write table " {} &            All & Female &    Male &        ACS \\"_n
file write table " \midrule " _n

local c = 0
foreach x of local demvar {		
	local ++c
		
	*Label
	local xtext: word `c' of "`labels'"
	if "`x'" == "age" | "`x'" == "hh_income_medians" {		
		sum `x' if acs == 0, d
		local x1: di %3.2f `r(p50)'
		local y1: di %3.2f `r(mean)'
		local z1: di %3.2f `r(sd)'
		sum `x' if acs == 0 & female == 1, d
		local x2: di %3.2f `r(p50)'
		local y2: di %3.2f `r(mean)'
		local z2: di %3.2f `r(sd)'
		sum `x' if acs == 0 & female == 0, d
		local x3: di %3.2f `r(p50)'
		local y3: di %3.2f `r(mean)'
		local z3: di %3.2f `r(sd)'
		sum `x' if acs == 1, d
		local x4: di %3.2f `r(p50)'
		local y4: di %3.2f `r(mean)'
		local z4: di %3.2f `r(sd)'
		median `x', by(acs)
		loc ds = `r(p)'
		local star
		if `ds'<0.1 local star "*"
		if `ds'<0.05 local star "**"
		if `ds'<0.01 local star "***"
		file write table "`xtext' & `z1'$ds^{`star'}$ds & `z2' & `z3' & `z4' \\" _n
		file write table " & [`x1']$ds^{`star'}$ds & [`x2'] & [`x3'] & [`x4'] \\" _n
		file write table " & (`z1') & (`z2') & (`z3') & (`z4') \\" _n
	}
	else {
		sum `x' if acs == 0
		local x1: di %3.2f `r(mean)'
		sum `x' if acs == 0 & female == 1
		local x2: di %3.2f `r(mean)'
		sum `x' if acs == 0 & female == 0
		local x3: di %3.2f `r(mean)'
		sum `x' if acs == 1
		local x4: di %3.2f `r(mean)'
		reg `x' acs, r
		local ds = el(r(table),4,1)
		local star
		if `ds'<0.1 local star "*"
		if `ds'<0.05 local star "**"
		if `ds'<0.01 local star "***"
		file write table "`xtext' & `x1'$ds^{`star'}$ds & `x2' & `x3' & `x4' \\" _n	
	}
}
file write table "\hline" _n
count if acs == 0
local calls: di %15.0fc r(N)
count if acs == 0 & female== 1
local cfs: di %15.0fc r(N)
count if acs == 0 & female== 0
local cms: di %15.0fc r(N)
count if acs == 1
local cacs: di %15.0fc r(N)
file write table "Obs & `calls' & `cfs' & `cms' & `cacs' \\"_n
file write table "\bottomrule" _n
file write table "\end{tabular}" _n
cap file close table
